﻿using System;
using System.IO;
using System.Data;
using System.Reflection;
using System.Diagnostics;
using cfg = System.Configuration;
using Excel;
using System.Windows.Forms;

namespace ww.wwf.wwfbll
{
    /// <summary>
    /// 功能说明：套用模板输出Excel，并对数据进行分页
    /// 作    者：Lingyun_k
    /// 创建日期：2005-7-12
    /// </summary>
    public class ExcelHelper
    {
        protected string templetFile = null;
        protected string outputFile = null;
        protected object missing = Missing.Value;
       // string strTemExcelFileName = @"C:\Excel导出模板.xls";
        string strTemExcelFileName = "";
        
        /// <summary>
        /// 构造函数，需指定模板文件和输出文件完整路径
        /// </summary>
        /// <param name="templetFilePath">Excel模板文件路径</param>
        /// <param name="outputFilePath">输出Excel文件路径</param>
        public ExcelHelper(string templetFilePath, string outputFilePath)
        {
            strTemExcelFileName = System.Environment.CurrentDirectory + @"\TemExcel.xls";
            
            if (outputFilePath == null)
                throw new Exception("输出Excel文件路径不能为空！");
            this.outputFile = outputFilePath;


            if (System.IO.File.Exists(templetFilePath))
            {
                this.templetFile = templetFilePath;
                //MessageBox.Show("创建一个新的模板文件");                
            }
            else
            {
                if (System.IO.File.Exists(strTemExcelFileName))
                {
                    this.templetFile = strTemExcelFileName;
                }
                else
                {
                    CreateExcelFile();
                    this.templetFile = strTemExcelFileName;
                }

            }
        }
       /// <summary>
       /// 创建Excel文件
       /// </summary>
        private void CreateExcelFile()
        {
            
            Excel.Application app = new Excel.Application();
            try
            {
                //让后台执行设置为不可见
                app.Visible = false;
                //新增加一个工作簿
                Workbook wBook = app.Workbooks.Add(true);

                //如果要打开已有的工作簿，则使用下面的注释语句
                // Workbook wBook = app.Workbooks.Open(@"C:\YourPath\YourWorkbook.xls",
                //    missing, missing, missing, missing, missing, missing, missing,
                //    missing, missing, missing, missing, missing,missing, missing);

                //取得一个工作表
                //如果打开了已有的工作簿,也可以这样获取工作表Worksheet wSheet = wBook.ActiveSheet as Worksheet
                Worksheet wSheet = wBook.Worksheets[1] as Worksheet;
                //wSheet.Cells[1, 1] = "this is a test";
                //设置禁止弹出保存和覆盖的询问提示框
                app.DisplayAlerts = false;
                app.AlertBeforeOverwriting = false;
                //保存工作簿
                wBook.Save();
                //保存excel文件
                app.Save(strTemExcelFileName);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message.ToString());
            }
            finally
            {
                //确保Excel进程关闭
                app.Quit();
                app = null;
            }
        }

        /// <summary>
        /// 将DataTable数据写入Excel文件（套用模板并分页）
        /// </summary>
        /// <param name="dt">DataTable</param>
        /// <param name="rows">每个WorkSheet写入多少行数据</param>
        /// <param name="top">行索引</param>
        /// <param name="left">列索引</param>
        /// <param name="sheetPrefixName">WorkSheet前缀名，比如：前缀名为“Sheet”，那么WorkSheet名称依次为“Sheet-1，Sheet-2...”</param>
        public void DataTableToExcel(System.Data.DataTable dt, int rows, int top, int left, string sheetPrefixName)
        {

            int rowCount = dt.Rows.Count;		//源DataTable行数
            int colCount = dt.Columns.Count;	//源DataTable列数
            int sheetCount = this.GetSheetCount(rowCount, rows);	//WorkSheet个数
            DateTime beforeTime;
            DateTime afterTime;

            if (sheetPrefixName == null || sheetPrefixName.Trim() == "")
                sheetPrefixName = "Sheet";

            //创建一个Application对象并使其可见
            beforeTime = DateTime.Now;
            Excel.Application app = new Excel.ApplicationClass();
           

            app.Visible = true;
            afterTime = DateTime.Now;

            //打开模板文件，得到WorkBook对象
            Excel.Workbook workBook = app.Workbooks.Open(templetFile, missing, missing, missing, missing, missing,
                                missing, missing, missing, missing, missing, missing, missing);

            //得到WorkSheet对象
            Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1);

            //复制sheetCount-1个WorkSheet对象
            for (int i = 1; i < sheetCount; i++)
            {
                ((Excel.Worksheet)workBook.Worksheets.get_Item(i)).Copy(missing, workBook.Worksheets[i]);
            }

            #region 将源DataTable数据写入Excel
            for (int i = 1; i <= sheetCount; i++)
            {
                int startRow = (i - 1) * rows;		//记录起始行索引
                int endRow = i * rows;			//记录结束行索引

                //若是最后一个WorkSheet，那么记录结束行索引为源DataTable行数
                if (i == sheetCount)
                    endRow = rowCount;

                //获取要写入数据的WorkSheet对象，并重命名
                Excel.Worksheet sheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
                sheet.Name = sheetPrefixName + "-" + i.ToString();

                //将dt中的数据写入WorkSheet
                for (int j = 0; j < endRow - startRow; j++)
                {
                    for (int k = 0; k < colCount; k++)
                    {
                        sheet.Cells[top + j, left + k] = dt.Rows[startRow + j][k].ToString();
                    }
                }

                //写文本框数据
               // Excel.TextBox txtDept = (Excel.TextBox)sheet.TextBoxes("txtDept");
               // Excel.TextBox txtDate = (Excel.TextBox)sheet.TextBoxes("txtDate");
                //Excel.TextBox txtPerson = (Excel.TextBox)sheet.TextBoxes("txtPerson");
                //Excel.TextBox txtDept = (Excel.TextBox)sheet.TextBoxes("txtDept");
               // txtPerson.Text = PersonGZForm.strPerson;
                //txtDept.Text = PersonGZForm.strDept;
               // txtDate.Text = TaoNet.Client.Web.NC.NCComm.strCurrDate;
              //  txtVersion.Text = "1.0.0.0";
            }
            #endregion

            //输出Excel文件并退出
            try
            {
                workBook.SaveAs(outputFile, missing, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing);
                workBook.Close(null, null, null);
                app.Workbooks.Close();
                app.Application.Quit();
                app.Quit();

                System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(app);

                workSheet = null;
                workBook = null;
                app = null;

                GC.Collect();
            }
            catch (Exception e)
            {
                System.IO.File.Delete(this.strTemExcelFileName);
                throw e;
            }
            finally
            {
                Process[] myProcesses;
                DateTime startTime;
                myProcesses = Process.GetProcessesByName("Excel");

                //得不到Excel进程ID，暂时只能判断进程启动时间
                foreach (Process myProcess in myProcesses)
                {
                    startTime = myProcess.StartTime;

                    if (startTime > beforeTime && startTime < afterTime)
                    {
                        myProcess.Kill();
                    }
                }
            }

        }


        /// <summary>
        /// 获取WorkSheet数量
        /// </summary>
        /// <param name="rowCount">记录总行数</param>
        /// <param name="rows">每WorkSheet行数</param>
        private int GetSheetCount(int rowCount, int rows)
        {
            int n = rowCount % rows;		//余数

            if (n == 0)
                return rowCount / rows;
            else
                return Convert.ToInt32(rowCount / rows) + 1;
        }


        /// <summary>
        /// 将二维数组数据写入Excel文件（套用模板并分页）
        /// </summary>
        /// <param name="arr">二维数组</param>
        /// <param name="rows">每个WorkSheet写入多少行数据</param>
        /// <param name="top">行索引</param>
        /// <param name="left">列索引</param>
        /// <param name="sheetPrefixName">WorkSheet前缀名，比如：前缀名为“Sheet”，那么WorkSheet名称依次为“Sheet-1，Sheet-2...”</param>
        public void ArrayToExcel(string[,] arr, int rows, int top, int left, string sheetPrefixName)
        {
            int rowCount = arr.GetLength(0);		//二维数组行数（一维长度）
            int colCount = arr.GetLength(1);	//二维数据列数（二维长度）
            int sheetCount = this.GetSheetCount(rowCount, rows);	//WorkSheet个数
            DateTime beforeTime;
            DateTime afterTime;

            if (sheetPrefixName == null || sheetPrefixName.Trim() == "")
                sheetPrefixName = "Sheet";

            //创建一个Application对象并使其可见
            beforeTime = DateTime.Now;
            Excel.Application app = new Excel.ApplicationClass();
            app.Visible = true;
            afterTime = DateTime.Now;

            //打开模板文件，得到WorkBook对象
            Excel.Workbook workBook = app.Workbooks.Open(templetFile, missing, missing, missing, missing, missing,
                missing, missing, missing, missing, missing, missing, missing);

            //得到WorkSheet对象
            Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1);

            //复制sheetCount-1个WorkSheet对象
            for (int i = 1; i < sheetCount; i++)
            {
                ((Excel.Worksheet)workBook.Worksheets.get_Item(i)).Copy(missing, workBook.Worksheets[i]);
            }

            #region 将二维数组数据写入Excel
            for (int i = 1; i <= sheetCount; i++)
            {
                int startRow = (i - 1) * rows;		//记录起始行索引
                int endRow = i * rows;			//记录结束行索引

                //若是最后一个WorkSheet，那么记录结束行索引为源DataTable行数
                if (i == sheetCount)
                    endRow = rowCount;

                //获取要写入数据的WorkSheet对象，并重命名
                Excel.Worksheet sheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
                sheet.Name = sheetPrefixName + "-" + i.ToString();

                //将二维数组中的数据写入WorkSheet
                for (int j = 0; j < endRow - startRow; j++)
                {
                    for (int k = 0; k < colCount; k++)
                    {
                        sheet.Cells[top + j, left + k] = arr[startRow + j, k];
                    }
                }

                Excel.TextBox txtAuthor = (Excel.TextBox)sheet.TextBoxes("txtAuthor");
                Excel.TextBox txtDate = (Excel.TextBox)sheet.TextBoxes("txtDate");
                Excel.TextBox txtVersion = (Excel.TextBox)sheet.TextBoxes("txtVersion");

                txtAuthor.Text = "lingyun_k";
                txtDate.Text = DateTime.Now.ToShortDateString();
                txtVersion.Text = "1.0.0.0";
            }
            #endregion

            //输出Excel文件并退出
            try
            {
                workBook.SaveAs(outputFile, missing, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing);
                workBook.Close(null, null, null);
                app.Workbooks.Close();
                app.Application.Quit();
                app.Quit();

                System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(app);

                workSheet = null;
                workBook = null;
                app = null;

                GC.Collect();
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                Process[] myProcesses;
                DateTime startTime;
                myProcesses = Process.GetProcessesByName("Excel");

                //得不到Excel进程ID，暂时只能判断进程启动时间
                foreach (Process myProcess in myProcesses)
                {
                    startTime = myProcess.StartTime;

                    if (startTime > beforeTime && startTime < afterTime)
                    {
                        myProcess.Kill();
                    }
                }
            }

        }

        public string DbGuid()
        {
            string strGuid = "";
            //string strGuid = System.Guid.NewGuid().ToString().ToUpper();
            strGuid = System.Guid.NewGuid().ToString();
            return strGuid.Replace("-", "");
        }
        /// <summary>
        /// dataGridView 值导出到 Excel
        /// </summary>
        /// <param name="dgvExcel">DataGridView</param>
        /// <param name="strftemplate">模板名</param>
        /// <param name="intTop">上开始行</param>
        /// <param name="intLeft">右开始列</param>
        public void GridViewToExcel(DataGridView dgvExcel, string strftemplate, int intTop, int intLeft)
        {
           
            
                //this.Validate();
                //dgvExcel.EndEdit();
                System.Data.DataTable dtOut = new System.Data.DataTable();
                string strColumnName = "";//列表

                DataRow drRow = dtOut.NewRow();
                for (int i = 0; i < dgvExcel.ColumnCount; i++)
                {
                    if (dgvExcel.Columns[i].Visible == true)
                    {
                        strColumnName = dgvExcel.Columns[i].HeaderText.ToString();
                        DataColumn column = new DataColumn(strColumnName, typeof(string));
                        dtOut.Columns.Add(column);
                        drRow[strColumnName] = strColumnName;
                    }
                }
                dtOut.Rows.Add(drRow);//加第一行             

                for (int ii = 0; ii < dgvExcel.Rows.Count; ii++)
                {
                    DataRow drDecRow = dtOut.NewRow();
                    for (int iii = 0; iii < dgvExcel.ColumnCount; iii++)
                    {
                        if (dgvExcel.Columns[iii].Visible == true)
                        {
                            string strC = dgvExcel.Columns[iii].HeaderText.ToString();
                            drDecRow[strC] = dgvExcel.Rows[ii].Cells[iii].Value.ToString();
                        }
                    }
                    dtOut.Rows.Add(drDecRow);
                }
                /*
                if (System.IO.File.Exists(this.strftemplate))
                {                    
                }
                else
                {
                    //WWMessage.MessageShowWarning("未设置模板文件: " + this.strftemplate);                   
                }*/

                string strFilePath = "";
                SaveFileDialog saveFileDialog = new SaveFileDialog();
                saveFileDialog.Filter = "XLS文件|*.xls|所有文件|*.*";
                saveFileDialog.FilterIndex = 1;
                saveFileDialog.RestoreDirectory = true;
                if (saveFileDialog.ShowDialog() == DialogResult.OK)
                {
                    strFilePath = saveFileDialog.FileName;
                    ExcelHelper eh = new ExcelHelper(strftemplate, strFilePath);
                    eh.DataTableToExcel(dtOut, dtOut.Rows.Count, intTop, intLeft, "页");
                    //if (WWMessage.MessageDialogResult("导出Excel文件成功：" + strFilePath + "\r\n\n 是否打开此文件：" + strFilePath + "?"))
                    //{
                       // System.Diagnostics.Process.Start(strFilePath);
                    //}
                }

          
        }
        /// <summary>
        /// dataGridView 值导出到 Excel 列名显示编号
        /// </summary>
        /// <param name="dgvExcel">DataGridView</param>
        /// <param name="strftemplate">模板名</param>
        /// <param name="intTop">上开始行</param>
        /// <param name="intLeft">右开始列</param>
        public void GridViewToExcelShowNum(DataGridView dgvExcel, string strftemplate, int intTop, int intLeft,string strDate)
        {
            //this.Validate();
            //dgvExcel.EndEdit();
            System.Data.DataTable dtOut = new System.Data.DataTable();
            string strColumnName = "";//列表

            DataRow drRow = dtOut.NewRow();
            for (int i = 0; i < dgvExcel.ColumnCount; i++)
            {
                if (dgvExcel.Columns[i].Visible == true)
                {
                    strColumnName = dgvExcel.Columns[i].HeaderText.ToString();
                    DataColumn column = new DataColumn(strColumnName, typeof(string));
                    dtOut.Columns.Add(column);
                   // drRow[strColumnName] = strColumnName;
                    drRow[strColumnName] = i+1;
                }
            }
            dtOut.Rows.Add(drRow);//加第一行             

            for (int ii = 0; ii < dgvExcel.Rows.Count; ii++)
            {
                DataRow drDecRow = dtOut.NewRow();
                for (int iii = 0; iii < dgvExcel.ColumnCount; iii++)
                {
                    if (dgvExcel.Columns[iii].Visible == true)
                    {
                        string strC = dgvExcel.Columns[iii].HeaderText.ToString();
                        drDecRow[strC] = dgvExcel.Rows[ii].Cells[iii].Value.ToString();
                    }
                }
                dtOut.Rows.Add(drDecRow);
            }
            /*
            if (System.IO.File.Exists(this.strftemplate))
            {                    
            }
            else
            {
                //WWMessage.MessageShowWarning("未设置模板文件: " + this.strftemplate);                   
            }*/

            string strFilePath = "";
            SaveFileDialog saveFileDialog = new SaveFileDialog();
            saveFileDialog.Filter = "XLS文件|*.xls|所有文件|*.*";
            saveFileDialog.FilterIndex = 1;
            saveFileDialog.RestoreDirectory = true;
            if (saveFileDialog.ShowDialog() == DialogResult.OK)
            {
                strFilePath = saveFileDialog.FileName;
                ExcelHelper eh = new ExcelHelper(strftemplate, strFilePath);
                eh.DataTableToExcelAddTitle(dtOut, dtOut.Rows.Count, intTop, intLeft, "页",strDate);
                //if (WWMessage.MessageDialogResult("导出Excel文件成功：" + strFilePath + "\r\n\n 是否打开此文件：" + strFilePath + "?"))
                //{
                // System.Diagnostics.Process.Start(strFilePath);
                //}
            }


        }
        /// <summary>
        /// 将DataTable数据写入Excel文件（套用模板并分页）
        /// </summary>
        /// <param name="dt">DataTable</param>
        /// <param name="rows">每个WorkSheet写入多少行数据</param>
        /// <param name="top">行索引</param>
        /// <param name="left">列索引</param>
        /// <param name="sheetPrefixName">WorkSheet前缀名，比如：前缀名为“Sheet”，那么WorkSheet名称依次为“Sheet-1，Sheet-2...”</param>
        public void DataTableToExcelAddTitle(System.Data.DataTable dt, int rows, int top, int left, string sheetPrefixName,string strDate)
        {

            int rowCount = dt.Rows.Count;		//源DataTable行数
            int colCount = dt.Columns.Count;	//源DataTable列数
            int sheetCount = this.GetSheetCount(rowCount, rows);	//WorkSheet个数
            DateTime beforeTime;
            DateTime afterTime;

            if (sheetPrefixName == null || sheetPrefixName.Trim() == "")
                sheetPrefixName = "Sheet";

            //创建一个Application对象并使其可见
            beforeTime = DateTime.Now;
            Excel.Application app = new Excel.ApplicationClass();


            app.Visible = true;
            afterTime = DateTime.Now;

            //打开模板文件，得到WorkBook对象
            Excel.Workbook workBook = app.Workbooks.Open(templetFile, missing, missing, missing, missing, missing,
                                missing, missing, missing, missing, missing, missing, missing);

            //得到WorkSheet对象
            Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1);

            //复制sheetCount-1个WorkSheet对象
            for (int i = 1; i < sheetCount; i++)
            {
                ((Excel.Worksheet)workBook.Worksheets.get_Item(i)).Copy(missing, workBook.Worksheets[i]);
            }

            #region 将源DataTable数据写入Excel
            for (int i = 1; i <= sheetCount; i++)
            {
                int startRow = (i - 1) * rows;		//记录起始行索引
                int endRow = i * rows;			//记录结束行索引

                //若是最后一个WorkSheet，那么记录结束行索引为源DataTable行数
                if (i == sheetCount)
                    endRow = rowCount;

                //获取要写入数据的WorkSheet对象，并重命名
                Excel.Worksheet sheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
                sheet.Name = sheetPrefixName + "-" + i.ToString();

                //将dt中的数据写入WorkSheet
                for (int j = 0; j < endRow - startRow; j++)
                {
                    for (int k = 0; k < colCount; k++)
                    {
                        sheet.Cells[top + j, left + k] = dt.Rows[startRow + j][k].ToString();
                    }
                }

                //写文本框数据
                // Excel.TextBox txtDept = (Excel.TextBox)sheet.TextBoxes("txtDept");                
                //Excel.TextBox txtPerson = (Excel.TextBox)sheet.TextBoxes("txtPerson");
                //Excel.TextBox txtDept = (Excel.TextBox)sheet.TextBoxes("txtDept");
                // txtPerson.Text = PersonGZForm.strPerson;
                //txtDept.Text = PersonGZForm.strDept;
               
                //  txtVersion.Text = "1.0.0.0";

                Excel.TextBox txtDate = (Excel.TextBox)sheet.TextBoxes("txtDate");
                txtDate.Text = strDate;
            }
            #endregion

            //输出Excel文件并退出
            try
            {
                workBook.SaveAs(outputFile, missing, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing);
                workBook.Close(null, null, null);
                app.Workbooks.Close();
                app.Application.Quit();
                app.Quit();

                System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(app);

                workSheet = null;
                workBook = null;
                app = null;

                GC.Collect();
            }
            catch (Exception e)
            {
                System.IO.File.Delete(this.strTemExcelFileName);
                throw e;
            }
            finally
            {
                Process[] myProcesses;
                DateTime startTime;
                myProcesses = Process.GetProcessesByName("Excel");

                //得不到Excel进程ID，暂时只能判断进程启动时间
                foreach (Process myProcess in myProcesses)
                {
                    startTime = myProcess.StartTime;

                    if (startTime > beforeTime && startTime < afterTime)
                    {
                        myProcess.Kill();
                    }
                }
            }

        }
    }
}
